<?php
class MysqlTools{
	
	//数据库配置
	private $dbHost;
	private $dbUserName;
	private $dbUserPwd;
	private $dbName;
	private $mysqli;
	
	public function __construct($dbHost,$dbUserName,$dbUserPwd,$dbName,$dbPort){
	    $this->dbHost = $dbHost;
	    $this->dbUserName = $dbUserName;
	    $this->dbUserPwd = $dbUserPwd;
	    $this->dbName = $dbName;
	    $this->dbPort = $dbPort;
	}
	
	/**
	 * 创建数据库连接
	 * @param array $dbConf
	 * @return resource
	 */
	public function createConnect(){
		try {
		    $this->mysqli = new mysqli($this->dbHost, $this->dbUserName, $this->dbUserPwd, $this->dbName,$this->dbPort);
		    $this->mysqli->query("SET NAMES 'UTF8'");
		    /* check connection */
		    if (mysqli_connect_errno()) {
		        printf("Connect failed: %s\n", mysqli_connect_error());
		        exit();
		    }
		    //******************************************************************
		} catch (\Exception $e) {
			echo $e->getMessage();
		}
	}
	
	/**
	 * 关闭数据库连接
	 * @param resource $conn
	 */
	public function closeConnect(){
		$this->mysqli->close();
	}
	
	/**
	 * 查询语句
	 * @param string $sql
	 * @param array $condition
	 * @return array
	 */
	public function selectSql($sql){
	    ChromePhp::log('select:'.$sql);
	    
	    try {
	        //装入执行的sql   
	        $result = $this->mysqli->query($sql);
	        //如果查询失败就直接返回布尔结果
	        if (is_bool($result))
	            return $result;
	        else{
	            $arrayData = array();
	            //使用一个数组保存数据
	            while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {		//MYSQL_ASSOC，MYSQL_NUM 和 MYSQL_BOTH
	                array_push($arrayData, $row);
	            }
	            	
	            mysqli_free_result($result);
	            return $arrayData;
	        }
	    } catch (Exception $e) {
	        ChromePhp::error($e->getMessage());
	    }
	}
	
	/**
	 * 缓存查询数据
	 * @param string $sql
	 * @return string
	 */
	function query_memcache ($sql)
	{
	    global $memcachehost,$memcacheport,$memcachelife;
	    $sql = ChromePhp::filterImpurityStr($sql);
	    $key = md5($sql);
	    $mem = new Memcache();
	    
	    $mem->connect($memcachehost, $memcacheport);    //$mem->connect(缓存主机,缓存端口)
	
	    if (! ($value = $mem->get($key))) { // Cache中没有,则从My SQL中查询
	        ChromePhp::log('select:'.$sql);
	        $microtime1 = microtime(); // 设置从数据库取之前的时间
	
	        //装入执行的sql
	        $result = $this->mysqli->query($sql);
	        //如果查询失败就直接返回布尔结果
	        if (is_bool($result)){
	           
	        }
	        else{
	            $arrayData = array();
	            //使用一个数组保存数据
	            while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {		//MYSQL_ASSOC，MYSQL_NUM 和 MYSQL_BOTH
	                array_push($arrayData, $row);
	            }
	        
	            mysqli_free_result($result);
	            $result = $arrayData;
	        }
	        
	        $value = $result;
	        $microtime2 = microtime(); // 设置从数据库取之后的时间
	        // 将Key和Value写入MemCache
	        $mem->set($key, $result, 0, $memcachelife);
	        $d_value = $microtime2 - $microtime1; // 返回一个时间差
	    } else {
	        $microtime3 = microtime(); // 设置从memcache中取之前的时间
	        $value = $mem->get($key);
	        $microtime4 = microtime(); // 设置从memcache中取之后的时间
	        $d_value = $microtime4 - $microtime3; // 返回一个时间差
	    }
	    return $value;
	}
	
	/**
	 * 修改语句
	 * @param string $sql
	 * @param array $condtion 查询条件集合
	 * @param array $sets 修改参数集合
	 * @return num
	 */
	public function executeSql($sql){
	    ChromePhp::log('execute:'.$sql);
	    try {
	        //装入执行的sql
	        $result = $this->mysqli->query($sql);
	    } catch (Exception $e) {
	        ChromePhp::error($e->getMessage());
	    }
		
		return $result;
	}
	
	/**
	 * 开启事务
	 */
	public function startTransaction(){
	    //事务开始
	    $this->mysqli->query("SET AUTOCOMMIT=0");
	    $this->mysqli->query("BEGIN");
		//mysqli_query('start transaction');
	}
	
	/**
	 * 提交事务
	 */
	public function commitTransaction(){
		$this->mysqli->query('commit');
	}
	
	/**
	 * 回滚事务
	 */
	public function rollback(){
		$this->mysqli->query('rollback');
	}
	
	/**
	 * 检查传入的参数是否安全
	 * @param string $value
	 * @return string
	 */
	public function validateParams($values=array(),$rules=array()) {
		$resultObj = [
			"result"=>false,
			"reason"=>""
		];
		
		foreach($values as $key => $value) {
			foreach ($rules as $rule){
				if($rule["ruleKey"]==$key){
					switch ($rule["ruleType"]){
						case "email":
							break;
						case "phone":
							break;
						default:
							break;
					}
				}
			}
		}
		
		return $value;
	}
	
	/**
	 * 检查传入的参数是否安全
	 * @param string $value
	 * @return string
	 */
	public function check_input($value='',$like=array()) {
		if(!empty($like))
			$value = $this->check_like($value, $like);
		// 去除斜杠
		if (get_magic_quotes_gpc()) {
			$value = stripslashes($value);
		}
			
		if(is_array($value)) {
			foreach($value as $key => $val) {
					$value[$key] = $this->check_input($val);
			}
		} else {
			// 去空格
			$value = trim($value);
			// 如果不是数字则加引号
			if (!is_numeric($value) && !$value=='')
				$value = "'" . $this->mysqli->real_escape_string($value) . "'";
		}
		//把所有为空的执成null
		if($value=='')$value=null;
		return $value;
	}
	
	/**
	 * 处理null值
	 * @param string $value
	 * @return string
	 */
	public function check_null($value) {
		foreach($value as $key => $val) {
			if($value[$key]==null){
				$value[$key] = 'null';
			}
		}
		return $value;
	}
	
	/**
	 * 处理like语句
	 * @param string $value
	 * @return string
	 */
	public function check_like($value,$likeArray) {
		if(is_array($likeArray)) {
			foreach($likeArray as $key => $val) {
				if(array_key_exists($val,$value)){
					if(!empty($value[$val]))
						$value[$val] = '%'.trim($value[$val]).'%';
				}
			}
		}else{
			if(array_key_exists($likeArray,$value))
				$value[$likeArray] = '%'.trim($value[$likeArray]).'%';
		}
		return $value;
	}
	
	/**
	 * 码表数据查询通用方法
	 */
	public function code_query_common($tableName){
	    global $mysqlTools;
	    $codeList = $mysqlTools->selectSql("
	        SELECT
            	id,
            	name,
            	c.`desc`,
            	isUsed,
            	c.`order`
            FROM
            	$tableName c    
	    ");
	    return $codeList;
	}
	
}
?>